#!/bin/bash
# dws_1d_to_dws_td_init.sh all/表名 日期
# 1、判断参数是否传入
if [ $# -lt 2 ]; then
    echo "必须输入 all/表名 和 日期"
    exit 1
fi

table_name=$1
date=$2

# 验证日期格式是否为 YYYY-MM-DD
if ! date -d "$date" +%Y-%m-%d >/dev/null 2>&1; then
    echo "日期格式错误，请使用 YYYY-MM-DD 格式"
    exit 1
fi

# 定义函数来执行 SQL
execute_sql() {
    local sql=$1
    /opt/module/hive/bin/hive -e "use medical;set hive.exec.dynamic.partition.mode=nonstrict;$sql"
    if [ $? -ne 0 ]; then
        echo "执行 SQL 失败: $sql"
        exit 1
    fi
}

# 根据表名匹配加载数据
case $table_name in
    "all")
        # 10.3.1 交易域医生粒度问诊历史至今汇总表 首日装载
        sql="insert overwrite table dws_trade_doctor_consultation_td
    partition (dt = '$date')
select doctor_id,
       doctor_name,
       min(dt) first_consultation_dt
from dws_trade_doctor_consultation_1d
group by doctor_id,
         doctor_name;"
        execute_sql "$sql"

        # 10.3.2 互动域医院用户粒度用户评价历史至今汇总表 首日装载
        sql="insert overwrite table dws_interaction_hospital_user_review_td
    partition (dt = '$date')
select hospital_id,
       name hospital_name,
       user_id,
       username,
       first_review_dt
from (select hospital_id,
             user_id,
             min(review.dt) first_review_dt
      from (select doctor_id,
                   user_id,
                   dt
            from dwd_interaction_review_inc) review
               left join (select id,
                                 hospital_id
                          from dim_doctor_full
                          where dt = '$date') doc
                         on review.doctor_id = doc.id
      group by hospital_id,
               user_id) avg
         left join (select id,
                           name
                    from dim_hospital_full
                    where dt = '$date') hos
                   on avg.hospital_id = hos.id
         left join (select id,
                           username
                    from dim_user_full
                    where dt = '$date') \`user\`
                   on avg.user_id = \`user\`.id;"
        execute_sql "$sql"

        # 10.3.3 互动域医院粒度用户评价历史至今汇总表 首日装载
        sql="insert overwrite table dws_interaction_hospital_review_td
    partition (dt = '$date')
select hospital_id,
       name hospital_name,
       review_count,
       good_review_count
from (select hospital_id,
             count(*)                  review_count,
             sum(if(rating = 5, 1, 0)) good_review_count
      from (select doctor_id,
                   rating
            from dwd_interaction_review_inc) review
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on review.doctor_id = doc.id
      group by hospital_id) avg
         left join (select id,
                           name
                    from dim_hospital_full
                    where dt = '$date') hos
                   on hospital_id = hos.id;"
        execute_sql "$sql"
        ;;
    "dws_trade_doctor_consultation_td")
        sql="insert overwrite table dws_trade_doctor_consultation_td
    partition (dt = '$date')
select doctor_id,
       doctor_name,
       min(dt) first_consultation_dt
from dws_trade_doctor_consultation_1d
group by doctor_id,
         doctor_name;"
        execute_sql "$sql"
        ;;
    "dws_interaction_hospital_user_review_td")
        sql="insert overwrite table dws_interaction_hospital_user_review_td
    partition (dt = '$date')
select hospital_id,
       name hospital_name,
       user_id,
       username,
       first_review_dt
from (select hospital_id,
             user_id,
             min(review.dt) first_review_dt
      from (select doctor_id,
                   user_id,
                   dt
            from dwd_interaction_review_inc) review
               left join (select id,
                                 hospital_id
                          from dim_doctor_full
                          where dt = '$date') doc
                         on review.doctor_id = doc.id
      group by hospital_id,
               user_id) avg
         left join (select id,
                           name
                    from dim_hospital_full
                    where dt = '$date') hos
                   on avg.hospital_id = hos.id
         left join (select id,
                           username
                    from dim_user_full
                    where dt = '$date') \`user\`
                   on avg.user_id = \`user\`.id;"
        execute_sql "$sql"
        ;;
    "dws_interaction_hospital_review_td")
        sql="insert overwrite table dws_interaction_hospital_review_td
    partition (dt = '$date')
select hospital_id,
       name hospital_name,
       review_count,
       good_review_count
from (select hospital_id,
             count(*)                  review_count,
             sum(if(rating = 5, 1, 0)) good_review_count
      from (select doctor_id,
                   rating
            from dwd_interaction_review_inc) review
               left join
           (select id,
                   hospital_id
            from dim_doctor_full
            where dt = '$date') doc
           on review.doctor_id = doc.id
      group by hospital_id) avg
         left join (select id,
                           name
                    from dim_hospital_full
                    where dt = '$date') hos
                   on hospital_id = hos.id;"
        execute_sql "$sql"
        ;;
    *)
        echo "不支持的表名: $table_name"
        exit 1
        ;;
esac

echo "首日数据加载完成"
